package com.hongsheng.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hongsheng.pojo.Bill;
import com.hongsheng.pojo.vo.OrderReportVo;
import org.apache.ibatis.annotations.*;

import java.io.IOException;
import java.util.List;

/**
 * ClassName:BillMapper
 * Description:
 *
 * @Author: 李媛芝红豆西米露
 * @Create: 2023/8/10 - 18:52
 * @Version: 2023.1.2
 */
@Mapper
public interface BillMapper extends BaseMapper<Bill> {
//查询所有
    @Select("select * from tb_bill")
     List<Bill> findAll();
//    按id查询
    @Select("select * from tb_bill where id=#{id}")
    Bill findBillById(int id);
//    按账单类型查询
    @Select("select * from tb_bill where type=#{type}")
    List<Bill> findBillByType(String type);
//    按账单金额区间查询
    @Select("select * from tb_bill where amount>= #{amount1} and amount<=#{amount2}")
    List<Bill> findByAmount(@Param("amount1")int amount1, @Param("amount2")int amount2);
    /**
     * 添加数据
     */
    @Update("insert into tb_bill values(null,#{type},#{billDesc},#{amount},#{createTime})")
    int insertBill(Bill bill);
    /**
     * 修改数据
     */
    @Update("update tb_bill set type=#{type},bill_desc=#{billDesc},amount=#{amount} where id=#{id}")
    int updateBill(Bill bill);

    /**
     * 删除数据
     */
    @Delete("delete from tb_bill where id=#{id}")
    int deleteBill(int id);

    /**
     * 查询账单月收入
     * @return
     */
    @Select("SELECT sum(amount) AS amount,MONTH ( create_time ) AS `month` FROM `tb_bill` WHERE type = '收入' and year(create_time)=2023 GROUP BY MONTH (create_time) ORDER BY `month`")
    @Results({
            @Result(property = "value",column = "amount"),
            @Result(property = "date",column = "month")
    })
    List<OrderReportVo>  selectBillByMonth();

    /**
     * 查询账单月支出
     * @return
     */
    @Select("SELECT sum(amount) AS amount,MONTH ( create_time ) AS `month` FROM `tb_bill` WHERE type = '支出' and year(create_time)=2023 GROUP BY MONTH (create_time) ORDER BY `month`")
    @Results({
            @Result(property = "value",column = "amount"),
            @Result(property = "date",column = "month")
    })
    List<OrderReportVo> selectBillByMoney();
}
